/**
 * 
 */
package com.imagineanddo.demo.persistence.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author www.imagineanddo.com
 *
 */
public class crearBD {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
	    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	    String dbName = "iadDemoDerby";
	    String dbParam = "create=true"; //Si la base de datos no existe, se crear� una nueva
	    String dbDirectory = ".\\Derby\\";
	    String connectionURL = "jdbc:derby:"+dbDirectory + dbName + ";" + dbParam;
	    /*
	    Si no se especifica la ruta donde se crear� la base de datos,
	    por defecto se crear� en la misma carpeta donde se encuentra el derby.jar
	    En ese caso la cadena de conexi� ser�a la siguiente:
	    String connectionURL = "jdbc:derby:" + dbName + ";" + dbParam;
	    */
	    Connection conn = null;
	    try{
	      Class.forName(driver);
	    } catch(java.lang.ClassNotFoundException e) {
	      e.printStackTrace();
	    }
	    try {
	      conn = DriverManager.getConnection(connectionURL);
	      Statement st = conn.createStatement();
              ResultSet tables = conn.getMetaData().getTables(conn.getCatalog(), null, null, null);
	      String sqlCreateTableUsers =
	             "CREATE TABLE users ( " +
	             "FirstName VARCHAR(20) NOT NULL, " +
	             "LastName VARCHAR(20) NOT NULL, " +
	             "idUser INTEGER NOT NULL CONSTRAINT idUser_PK PRIMARY KEY " +
	             ")";
	      if(tables==null) {
                    st.execute(sqlCreateTableUsers);
                    st.executeUpdate("INSERT INTO users VALUES('Juan', 'Perez', 1)");
                    st.executeUpdate("INSERT INTO users VALUES('Renzo', 'Lopez', 2)");
                    st.executeUpdate("INSERT INTO users VALUES('Carla', 'Mendivil', 3)");
                }
	      System.out.println("La base de datos '" + dbName + "' se ha creado correctamente");

              ResultSet rs = st.executeQuery("SELECT * FROM users"); 
                while (rs.next())
                {
                   System.out.println("nombre="+rs.getObject("FirstName")+
                      ", apellidos="+rs.getObject("LastName")+
                      ", idUser="+rs.getObject("idUser"));
                }
                rs.close();
	      
	    }  catch (Throwable e)  {
	      System.out.println("Error al crear la base de datos '" + dbName + "'");
	      e.printStackTrace();
	    } finally {
	      try { conn.close(); }
	      catch (Throwable t){}
	    }

	}

}
